using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
using NBusiness.Data;
using NBusiness.CodeDom;
using NBusiness.Templates;
using NBusiness.Utilities;
using NBusiness.Query;

namespace NBusiness.Providers
{
	public class SqlDatabaseProvider : IDatabaseProvider
    {
        #region Get Connection
        public virtual DbConnection GetConnection(string connectionString)
        {
            return new SqlConnection(connectionString);
        }
        #endregion

        #region Insert
        public virtual void Insert(DbTransaction transaction, string command, LoadIdentityHandler loadIdentity, params Parameter[] parameters)
		{
			using (DbCommand cmd = transaction.Connection.CreateCommand())
			{
				cmd.Transaction = transaction;
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = command;

				foreach (Parameter p in parameters)
				{
					InsertDbParam(cmd, p);
				}

                cmd.ExecuteNonQuery();

                if (loadIdentity != null)
                {
                    Dictionary<string, object> dicParams = new Dictionary<string, object>();
                    foreach (Parameter p in parameters)
                    {
                        if (p.Direction == ParameterDirection.Output || p.Direction == ParameterDirection.InputOutput)
                        {
                            dicParams.Add(p.Name, cmd.Parameters[p.Name].Value);
                        }
                    }
                    loadIdentity(dicParams);
                }
			}
		}
		#endregion

		#region Update
		public virtual void Update(DbTransaction transaction, string command, params Parameter[] parameters)
		{
			using (DbCommand cmd = transaction.Connection.CreateCommand())
			{
				cmd.Transaction = transaction;
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = command;
				foreach (Parameter p in parameters)
				{
					InsertDbParam(cmd, p);
				}

				cmd.ExecuteNonQuery();
			}
		}
		#endregion

		#region Fetch
        public virtual void Fetch(DbConnection cn, string command, CriteriaBase criteria, EventHandler<LoadEventArgs> load, params Parameter[] parameters)
        {
            using (DbCommand cmd = cn.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = command;
                foreach (Parameter p in parameters)
                {
                    InsertDbParam(cmd, p);
                }

                using (DataReader dr = new DataReader(cmd.ExecuteReader()))
                {
                    if (dr.Read())
                    {
                        if (load.Target is EntityBase)
                            (load.Target as EntityBase).MarkOld();

                        load(this, new LoadEventArgs(dr, criteria));
                    }
                }
            }
        }
        
        public virtual int FetchCollection(
            DbConnection connection, 
            string command, 
            CriteriaBase criteria,
            EventHandler<LoadEventArgs> load, 
            int startIndex, 
            int maxRows, 
            string sortField, 
            NBusiness.Query.SortOrder sortOrder, 
            params Parameter[] parameters)
		{
            if (connection == null)
                throw new ArgumentNullException("connection");
            if(string.IsNullOrEmpty(command))
                throw new ArgumentException("Invalid command");
            if (load == null)
                throw new ArgumentException("Invalid load method");
            
            int totalRows = 0;
			using (DbCommand cmd = connection.CreateCommand())
			{
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = command;
				foreach (Parameter p in parameters)
				{
                    InsertDbParam(cmd, p);
				}

                if (startIndex > -1 && maxRows > 0)
                {
                    InsertDbParam(cmd, "@StartIndex", startIndex);
                    InsertDbParam(cmd, "@MaxRows", maxRows);
                    InsertDbParam(cmd, "@TotalRows", 0, ParameterDirection.Output);
                }
                if (sortField != null && sortOrder != NBusiness.Query.SortOrder.None)
                {
                    InsertDbParam(cmd, "@SortField", sortField);
                    InsertDbParam(cmd, "@SortOrder", (int)sortOrder);
                }

				using (DataReader reader = new DataReader(cmd.ExecuteReader()))
				{
					load(this, new LoadEventArgs(reader, criteria));
				}
                if (cmd.Parameters.Contains("@TotalRows") &&
                    cmd.Parameters["@TotalRows"].Value != DBNull.Value)
                {
                    totalRows = Convert.ToInt32(cmd.Parameters["@TotalRows"].Value);
                }
			}

            return totalRows;
		}
        #endregion

        #region Query
        /// <summary>
        /// Performs a dynamic database call based on the query object passed into this method.
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="query"></param>
        /// <param name="load"></param>
        public virtual void Query(
            DbConnection connection,
            QueryCriteria query,
            EventHandler<LoadEventArgs> load)
        {

            if (connection == null)
                throw new ArgumentNullException("connection");
            if (query == null)
                throw new ArgumentException("Invalid command");
            if (load == null)
                throw new ArgumentException("Invalid load method");

            using (DbCommand cmd = connection.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = query.Query.ToSql();
                foreach (Parameter p in query.Parameters)
                {
                    InsertDbParam(cmd, p);
                }

                using (DataReader reader = new DataReader(cmd.ExecuteReader()))
                {
                    load(this, new LoadEventArgs(reader, query));
                }
            }
        }

		#endregion

		#region Delete
		public virtual void Delete(DbTransaction transaction, string command, params Parameter[] parameters)
		{
			using (DbCommand cmd = transaction.Connection.CreateCommand())
			{
				cmd.Transaction = transaction;
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = command;
				foreach (Parameter p in parameters)
				{
					InsertDbParam(cmd, p);
				}

				cmd.ExecuteNonQuery();
			}
		}
		#endregion

		#region Create Param
        private static void InsertDbParam(DbCommand cmd, Parameter p)
        {
            InsertDbParam(cmd, p.Name, p.Value, p.Direction);
        }
        private static void InsertDbParam(DbCommand cmd, string paramName, object value)
        {
            InsertDbParam(cmd, paramName, value, ParameterDirection.Input);
        }
        private static void InsertDbParam(DbCommand cmd, string paramName, object value, ParameterDirection direction)
        {
            DbParameter dbp = cmd.CreateParameter();
            dbp.Direction = direction;
            dbp.ParameterName = paramName;
            dbp.Value = (value != null ? value : DBNull.Value);
            cmd.Parameters.Add(dbp);
        }
		#endregion
    }
}
